﻿Imports System.Text 

Public Class DataAccessGeneric(Of T)
    Inherits DataAccessBase


    Public Sub Delete(ByVal idx As Object, ByVal PrimaryKeyName As String, ByVal TableName As String)
        Me.Open()
        Try
            Dim p = Me.getParam(Of Integer)(PrimaryKeyName, idx)
            Me.ExecuteTextNonQuery(String.Format("Delete from {0} where {1} = {2}", TableName, PrimaryKeyName, p.ParameterName), p)

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try

    End Sub

    'Public Sub UpdateOneParam(ByVal TableName As String, ByVal ParamToUpdateName As String, ByVal ParamValueToUpdate As Object, ByVal ParamToCompare As String, ByVal ParamToCompareValue As Object)
    '    Me.Open()
    '    Try

    '        Me.ExecuteTextNonQuery(String.Format("Update from {0} Set {1} = {2} where {3} = {4}", TableName, ParamToUpdateName, ), p, p2)

    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        Me.Close()
    '    End Try
    'End Sub


    'Public Function Add(ByVal objectInstance As T, Optional ByVal PrimaryKeyFieldName As String = Nothing, Optional ByVal TableName As String = Nothing) As Integer
    '    Me.Open()
    '    Try
    '        Dim arrToExclude As New List(Of String)
    '        Dim PrimaryKeyName As String = PrimaryKeyFieldName
    '        If String.IsNullOrEmpty(TableName) Then
    '            TableName = CType(CObj(Me.getCurrentDTO()), EntityBase).ClassAttribute.TableName
    '        End If
    '        If String.IsNullOrEmpty(PrimaryKeyName) Then
    '            'PrimaryKeyName =
    '            Dim pkCol = Me.getCurrentEntityPropertiesAttributes(). _
    '            Where(Function(x) x.Value.PrimaryKeyAtt IsNot Nothing)
    '            If pkCol.Any() Then
    '                PrimaryKeyName = pkCol.FirstOrDefault().Value.FieldMappingAtt.DbFieldMapp
    '                If String.IsNullOrEmpty(PrimaryKeyName) Then
    '                    PrimaryKeyName = pkCol.FirstOrDefault().Value.PropertyName
    '                End If
    '            End If
    '            If String.IsNullOrEmpty(PrimaryKeyName) Then
    '                Throw New ApplicationException("no se ha indicado la clave primaria")
    '            End If
    '        End If

    '        arrToExclude.AddRange(DirectCast(CObj(objectInstance), EntityBase).GetNotMappedProperties().Select(Function(x) x.Name).ToArray())

    '        Dim params As IEnumerable(Of IDataParameter)

    '        Dim sbParamsName As New StringBuilder()


    '        Select Case Me.ConnectionType
    '            Case 1
    '                arrToExclude.Add(PrimaryKeyName)

    '                params = Me.getParamFromObject(objectInstance, arrToExclude.ToArray())

    '                For Each p In params.Where(Function(x) Not arrToExclude.Contains(x.ParameterName))
    '                    sbParamsName.AppendFormat("{0},", p.ParameterName)
    '                Next

    '            Case 3
    '                params = Me.getParamFromObject(objectInstance, arrToExclude.ToArray())
    '                For Each p In params.Where(Function(x) Not arrToExclude.Contains(x.ParameterName))
    '                    sbParamsName.AppendFormat("{0},", p.ParameterName)
    '                Next

    '            Case Else
    '                arrToExclude.Add(PrimaryKeyName)
    '                params = Me.getParamFromObject(objectInstance, arrToExclude.ToArray())
    '                For Each p In params.Where(Function(x) Not arrToExclude.Contains(x.ParameterName))
    '                    sbParamsName.AppendFormat("{0},", p.ParameterName)
    '                Next

    '        End Select


    '        Select Case Me.ConnectionType
    '            Case 1
    '                arrToExclude.Add(PrimaryKeyName)
    '            Case 3
    '                Dim ds1 = Me.ExecuteSQLGetDataSet(String.Format("Select MAX({0}) from {1}", PrimaryKeyName, TableName))
    '                params.Where(Function(x) x.ParameterName.Equals(Me.getParameterPrefix & PrimaryKeyName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault().Value = CInt(ds1.Tables(0).Rows(0)(0)) + 1

    '            Case Else
    '                arrToExclude.Add(PrimaryKeyName)
    '        End Select


    '        Dim strSql = String.Format("Insert into {0} ({1}) Values ({2})", TableName, getAllPropertiesAsString(objectInstance, CInt(ConnectionType), arrToExclude.ToArray()), sbParamsName.Remove(sbParamsName.Length - 1, 1).ToString())

    '        Me.ExecuteTextNonQuery(strSql, params)
    '        Dim ds = Me.ExecuteSQLGetDataSet(String.Format("Select MAX({0}) from {1}", PrimaryKeyName, TableName))
    '        Return CInt(ds.Tables(0).Rows(0)(0))
    '    Catch ex As Exception
    '        Throw ex
    '    Finally
    '        Me.Close()
    '    End Try

    'End Function

    Private Sub verifySequence(ByVal tableName As String, ByVal PrimaryKeyName As String)
        Me.Open()
        Dim d1 As Double = 9999
        Try

            SyncLock New Object
               

                Dim key As String = String.Format("{0}.{1}", Me.ConnectionStringEntry, tableName)
                Dim isVerified = CacheMannager.getFromCache(Of DataAccessGeneric(Of T), Boolean)(key)
                If isVerified Then
                    Return
                End If
                Dim s1 = String.Format("Select NVL(MAX({0}),0) + 1 From {1}", PrimaryKeyName, tableName)
                d1 = CDbl(Me.ExecuteSQLGetDataSet(s1).Tables(0).Rows(0)(0))

                Dim strSql = "SELECT sequence_name FROM all_sequences where UPPER(sequence_name)  = '" & getSequenceName(tableName) & "' "
                Dim ds = Me.ExecuteSQLGetDataSet(strSql)
                If ds.Tables(0).Rows.Count = 0 Then
                    'strSql = "DROP SEQUENCE " & ds.Tables(0).Rows(0)("sequence_name")
                    'Me.ExecuteTextNonQuery(strSql)
                    strSql = "CREATE SEQUENCE " & getSequenceName(tableName) & "   MINVALUE 1    START WITH " & d1 & "   INCREMENT BY 1   CACHE 50"
                    Me.ExecuteTextNonQuery(strSql)
                End If 
                CacheMannager.SetInCache(Of DataAccessGeneric(Of T), Boolean)(key, True, -1)
            End SyncLock
        Catch
            Try
                Dim strSql = "CREATE SEQUENCE " & getSequenceName(tableName) & "   MINVALUE 1    START WITH " & d1 & "   INCREMENT BY 1   CACHE 50"
                Me.ExecuteTextNonQuery(strSql)
            Catch
            End Try
        Finally
            Me.Close()
        End Try

    End Sub


    Private Function getSequenceName(ByVal table As String) As String
        Return String.Format("gs{0}", table).ToUpper()
    End Function


    ' Private Shared sequences As New Dictionary(Of String, Double)
    Private Shared _isCalculating As New Dictionary(Of String, Boolean)

    Private Function getNextID(ByVal tableName As String, ByVal primaryKeyName As String) As Double
        Me.Open()
        Dim key = Me.ConnectionStringEntry & "." & tableName
        Try
             
            SyncLock New Object
                verifySequence(tableName, primaryKeyName)
                Dim dbl = Me.ExecuteSQLGetDataSet(String.Format("select {0}.nextval from dual", getSequenceName(tableName))).Tables(0).Rows(0)(0)
                Return dbl
            End SyncLock

        Catch ex As Exception
            Try
                verifySequence(tableName, primaryKeyName)
                Dim s1 = String.Format("Select NVL(MAX({0}),0) + 1 From {1}", primaryKeyName, tableName)
                Return CDbl(Me.ExecuteSQLGetDataSet(s1).Tables(0).Rows(0)(0))
            Catch ex2 As Exception
                Throw ex2
            End Try
        Finally
            Me.Close()

        End Try
    End Function

    Private Shared _isAdding As New Dictionary(Of String, Boolean)
    Private Function isAdding(ByVal key As String) As Boolean
        If _isAdding.ContainsKey(key) Then
            Return _isAdding(key)
        Else
            _isAdding.Add(key, False)
        End If
    End Function

    Public Function Add(ByVal objectInstance As T, Optional ByVal PrimaryKeyFieldName As String = Nothing, Optional ByVal TableName As String = Nothing) As Integer
        Me.Open()
        Dim key = String.Format("{0},{1},{2}", objectInstance.GetType().FullName, PrimaryKeyFieldName, TableName)
        Try
            SyncLock New Object

                _isAdding(key) = True
                Dim arrToExclude As New List(Of String)
                Dim PrimaryKeyName As String = PrimaryKeyFieldName
                If String.IsNullOrEmpty(TableName) Then
                    TableName = CType(CObj(Me.getCurrentDTO()), EntityBase).ClassAttribute.TableName
                End If

                If String.IsNullOrEmpty(PrimaryKeyName) Then
                    'PrimaryKeyName =
                    Dim pkCol = Me.getCurrentEntityPropertiesAttributes(). _
                    Where(Function(x) x.Value.PrimaryKeyAtt IsNot Nothing)
                    If pkCol.Any() Then
                        PrimaryKeyName = pkCol.FirstOrDefault().Value.FieldMappingAtt.DbFieldMapp
                        If String.IsNullOrEmpty(PrimaryKeyName) Then
                            PrimaryKeyName = pkCol.FirstOrDefault().Value.PropertyName
                        End If
                    End If
                    If String.IsNullOrEmpty(PrimaryKeyName) Then
                        Throw New ApplicationException("no se ha indicado la clave primaria")
                    End If
                End If
                arrToExclude.AddRange(DirectCast(CObj(objectInstance), EntityBase).GetNotMappedProperties().Select(Function(x) x.Name).ToArray())

                Select Case Me.ConnectionType
                    Case ConnectionTypeEnum.Oracle
                        Exit Select
                    Case Else
                        arrToExclude.Add(PrimaryKeyName)
                End Select

                Dim params = Me.getParamFromObject(objectInstance, arrToExclude.ToArray())

                Dim sbParamsName As New StringBuilder()

                For Each p In params.Where(Function(x) Not arrToExclude.Contains(x.ParameterName))
                    sbParamsName.AppendFormat("{0},", p.ParameterName)
                Next

                Select Case Me.ConnectionType
                    Case ConnectionTypeEnum.Oracle

                        'Dim ds1 = Me.ExecuteSQLGetDataSet(String.Format("Select NVL(MAX({0}),0) from {1}", PrimaryKeyName, TableName))
                        params.Where(Function(x) x.ParameterName.Equals(Me.getParameterPrefix & PrimaryKeyName, StringComparison.InvariantCultureIgnoreCase)).FirstOrDefault().Value = getNextID(TableName, PrimaryKeyName)

                End Select

                Dim strSql = String.Format("Insert into {0} ({1}) Values ({2})", TableName, getAllPropertiesAsString(objectInstance, CInt(Me.ConnectionType), arrToExclude.ToArray()), sbParamsName.Remove(sbParamsName.Length - 1, 1).ToString())

                Try
                    Me.ExecuteTextNonQuery(strSql, params)
                Catch ex As OracleException
                    Throw ex
                Catch ex As Exception
                    Throw ex
                End Try
                Dim ds = Me.ExecuteSQLGetDataSet(String.Format("Select MAX({0}) from {1}", PrimaryKeyName, TableName))
                Return CInt(ds.Tables(0).Rows(0)(0))
            End SyncLock
        Catch ex As Exception
            Throw ex
        Finally
            _isAdding(key) = False
            Me.Close()
        End Try

    End Function


    Public Function getDataTable(filtros As Object) As DataTable
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim p = MyBase.getParamFromObjectPropertyMapped(filtros)
            Dim sb As New StringBuilder()
            For Each item In p
                sb.AppendFormat("{0} = {1} AND ", item.Key, item.Value.ParameterName)
            Next



            Dim strSql As String = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1")
             
            Return Me.ExecuteSQLGetDataSet(strSql, p.Select(Function(x) x.Value).ToArray()).Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getDataTable() As DataTable
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim strSql As String = Me.getSelectQuery(TableName, "Where 1 = 1")
            Return Me.ExecuteSQLGetDataSet(strSql).Tables(0)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getTableName() As String
        Dim TableName As String

        If GetType(Entity.EntityBase).IsAssignableFrom(GetType(T)) Then
            Dim ebase = DirectCast(CObj(ReflectionHelper.GetEntityClassInstance(Of T)()), EntityBase)

            If ebase.ClassAttribute().isView Then
                If Me.ConnectionType = ConnectionTypeEnum.SQLServer Then
                    Return String.Format("({0}) As T1", ebase.ClassAttribute().ViewStatement)
                End If
                Return String.Format("({0})", ebase.ClassAttribute().ViewStatement)
            End If

            TableName = ebase.ClassAttribute().TableName
        Else
            TableName = GetType(T).Name
        End If
        Return TableName
    End Function

    Public Function getList() As IEnumerable(Of T)
        Me.Open()
        Try
            Dim TableName As String = getTableName()

            Dim strSql As String = Me.getSelectQuery(TableName, "Where 1 = 1")
            Return Me.ExecuteSQLGetList(Of T)(strSql)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getList(ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String) As PagedResults(Of T)
        Me.Open()
        Try
            Dim TableName As String = getTableName()

            Dim strSql As String() = Me.getSelectQuery(TableName, "Where 1 = 1", OrderByField, rowFrom, rowTo)  'Me.getSelectQuery(TableName, "Where 1 = 1")

            Dim pr = New PagedResults(Of T)
            pr.List = Me.ExecuteSQLGetList(Of T)(strSql(0))
            pr.RecordCount = getRecordCount(strSql(1), Nothing)
            Return pr
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function


    Public Function getListFiltered(ByVal FieldName As String, ByVal value As Object) As List(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim p = getParam(FieldName, value)

            Dim sb As New StringBuilder()

            sb.AppendFormat("{0} = {1} AND", FieldName, p.ParameterName)

            Dim strSql As String = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1")
            Return Me.ExecuteSQLGetList(Of T)(strSql, p)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getListFiltered(ByVal FieldName As String, ByVal value As Object, ByVal conditions As String) As List(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim p = getParam(FieldName, value)

            Dim sb As New StringBuilder()

            sb.AppendFormat("{0} = {1} AND", FieldName, p.ParameterName)
            If Not String.IsNullOrWhiteSpace(conditions) Then
                sb.AppendFormat("  {0} AND ", conditions)
            End If


            Dim strSql As String = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1")
            Return Me.ExecuteSQLGetList(Of T)(strSql, p)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getListFiltered(ByVal FieldName As String, ByVal value As Object, ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String) As PagedResults(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim p = getParam(FieldName, value)

            Dim sb As New StringBuilder()

            sb.AppendFormat("{0} = {1} AND", FieldName, p.ParameterName)

            Dim strSql As String() = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1", OrderByField, rowFrom, rowTo)

            Dim pr = New PagedResults(Of T)
            pr.List = Me.ExecuteSQLGetList(Of T)(strSql(0), p)
            pr.RecordCount = getRecordCount(strSql(1), p)
            Return pr

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    'ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String
    Public Function getList(ByVal AnonimousObject As Object) As IEnumerable(Of T)
        Me.Open()
        Try
            Dim TableName As String = CType(CObj(Me.getCurrentDTO), EntityBase).ClassAttribute.TableName


            Dim p = MyBase.getParamFromObjectPropertyMapped(AnonimousObject)
            Dim sb As New StringBuilder()
            For Each item In p
                sb.AppendFormat("{0} = {1} AND ", item.Key, item.Value.ParameterName)
            Next



            Dim strSql As String = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1")
            Return Me.ExecuteSQLGetList(Of T)(strSql, p.Select(Function(x) x.Value).ToArray())
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getList(ByVal AnonimousObject As Object, ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String) As PagedResults(Of T)
        Me.Open()
        Try
            Dim TableName As String = CType(CObj(Me.getCurrentDTO), EntityBase).ClassAttribute.TableName


            Dim p = MyBase.getParamFromObjectPropertyMapped(AnonimousObject)
            Dim sb As New StringBuilder()
            For Each item In p
                sb.AppendFormat("{0} = {1} AND ", item.Key, item.Value.ParameterName)
            Next



            Dim strSql As String() = Me.getSelectQuery(TableName, "Where " & sb.ToString() & " 1 = 1", OrderByField, rowFrom, rowTo)
            Dim pr = New PagedResults(Of T)
            pr.List = Me.ExecuteSQLGetList(Of T)(strSql(0), p.Select(Function(x) x.Value).ToArray())
            pr.RecordCount = getRecordCount(strSql(1), p.Select(Function(x) x.Value).ToArray())

            Return pr
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function







    Private Function getRecordCount(ByVal strSql As String, ByVal Parameters As IEnumerable(Of IDataParameter)) As Double
        Me.Open()
        Try
            Dim ds As DataSet
            If Parameters IsNot Nothing Then
                ds = Me.ExecuteSQLGetDataSet(strSql, Parameters.ToArray())
            Else
                ds = Me.ExecuteSQLGetDataSet(strSql)
            End If

            If ds.Tables(0).Rows.Count = 0 OrElse ds.Tables(0).Rows(0)(0) Is DBNull.Value Then
                Return 0
            End If

            Return CDbl(ds.Tables(0).Rows(0)(0))

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function


    Public Function getList(ByVal whereSql As String, ParamArray Parameters() As IDataParameter) As IEnumerable(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim strSql As String = Me.getSelectQuery(TableName, whereSql)
            If Parameters IsNot Nothing Then
                Return Me.ExecuteSQLGetList(Of T)(strSql, Parameters.ToList())
            Else
                Return Me.ExecuteSQLGetList(Of T)(strSql)
            End If

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function

    Public Function getList(ByVal whereSql As String, ByVal Parameters As IEnumerable(Of IDataParameter)) As IEnumerable(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim strSql As String = Me.getSelectQuery(TableName, whereSql)
            If Parameters IsNot Nothing Then
                Return Me.ExecuteSQLGetList(Of T)(strSql, Parameters.ToList())
            Else
                Return Me.ExecuteSQLGetList(Of T)(strSql)
            End If

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function
    '
    Public Function getList(ByVal whereSql As String, ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String, ByVal Parameters As IEnumerable(Of IDataParameter)) As PagedResults(Of T)
        Me.Open()
        Try
            Dim TableName As String

            TableName = getTableName()

            Dim strSql As String() = Me.getSelectQuery(TableName, whereSql, OrderByField, rowFrom, rowTo)
            Dim l As IEnumerable(Of T)
            If Parameters IsNot Nothing Then
                l = Me.ExecuteSQLGetList(Of T)(strSql(0), Parameters.ToList())
            Else
                l = Me.ExecuteSQLGetList(Of T)(strSql(0))
            End If

            Dim pr = New PagedResults(Of T)
            pr.List = l
            pr.RecordCount = getRecordCount(strSql(1), Parameters)
            Return pr

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function
    ', ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String, 

    Public Function getList(ByVal TableName As String, ByVal ObjectParam As Object) As IEnumerable(Of T)
        Me.Open()
        Try
            Dim lp = Me.getParamsFromDTO(ObjectParam)
            Dim sb As New StringBuilder()
            sb.AppendLine("Where ")
            For Each p In lp
                sb.AppendFormat("{0} = {1} AND ", p.Key, p.Value.ParameterName)
            Next
            sb.AppendLine("1=1")

            Dim strSql As String = Me.getSelectQuery(TableName, sb.ToString())
            Return Me.ExecuteSQLGetList(Of T)(strSql, lp)
        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function


    Public Function getList(ByVal TableName As String, ByVal ObjectParam As Object, ByVal rowFrom As UInt32, ByVal rowTo As UInt32, ByVal OrderByField As String) As PagedResults(Of T)
        Me.Open()
        Try
            Dim lp = Me.getParamsFromDTO(ObjectParam)
            Dim sb As New StringBuilder()
            sb.AppendLine("Where ")
            For Each p In lp
                sb.AppendFormat("{0} = {1} AND ", p.Key, p.Value.ParameterName)
            Next
            sb.AppendLine("1=1")

            Dim strSql As String() = Me.getSelectQuery(TableName, sb.ToString(), OrderByField, rowFrom, rowTo)

            Dim pr = New PagedResults(Of T)
            pr.List = Me.ExecuteSQLGetList(Of T)(strSql(0), lp)
            pr.RecordCount = getRecordCount(strSql(1), Nothing)
            Return pr


        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try
    End Function


    Public Function getOneByID(ByVal ID As Integer, _
                               Optional ByVal PrimaryKeyName As String = Nothing, _
                               Optional ByVal strTable As String = Nothing) As T

        Me.Open()
        Try
            If String.IsNullOrEmpty(strTable) Then
                strTable = getTableName()
            End If
            If String.IsNullOrEmpty(PrimaryKeyName) Then
                Throw New ApplicationException("no se ha indicado la clave primaria")
            End If



            Dim strWhere As String = String.Format("where {0} = {1}", PrimaryKeyName, ID)

            Dim strSql As String = Me.getSelectQuery(strTable, strWhere)


            Dim l As List(Of T) = Me.ExecuteSQLGetList(Of T)(strSql)

            If l IsNot Nothing AndAlso l.Any() Then
                Return l(0)
            Else
                Return Nothing
            End If

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try

    End Function


    Protected Friend Function getParamsFromDTO(ByVal obj As T) As Dictionary(Of String, IDataParameter)
        Dim l As New Dictionary(Of String, IDataParameter)

        Dim dtoList = DirectCast(CObj(obj), EntityBase).PropertiesAttributes()

        For Each item In dtoList
            Dim paramName As String
            Select Case Me.ConnectionType
                Case ConnectionTypeEnum.SQLCompact, ConnectionTypeEnum.SQLServer
                    paramName = getParameterPrefix() + item.Value.FieldMappingAtt.DbFieldMapp
                Case Else
                    paramName = item.Value.FieldMappingAtt.DbFieldMapp

            End Select
            Dim paramValue = item.Value.PropertyValue
            l.Add(item.Value.FieldMappingAtt.DbFieldMapp, getParam(paramName, paramValue, getDbType(item.Value.PropertyType)))
        Next


        Return l
    End Function

    Public Sub Update(ByVal objectInstance As T, Optional ByVal PrimaryKeyName As String = Nothing, Optional ByVal TableName As String = Nothing)
        Me.Open()
        Try

            If String.IsNullOrEmpty(TableName) Then
                TableName = getTableName()
            End If
            If String.IsNullOrEmpty(PrimaryKeyName) Then
                Throw New ApplicationException("no se ha indicado la clave primaria")
            End If

            Dim params As New Dictionary(Of String, IDataParameter)
            params = Me.getParamsFromDTO(objectInstance)

            Dim sbParamsName As New StringBuilder()

            For Each p In params.Where(Function(x) x.Key.Equals(PrimaryKeyName, StringComparison.CurrentCultureIgnoreCase) = False)
                sbParamsName.AppendFormat("{0} = {1},", p.Key, p.Value.ParameterName)
            Next

            sbParamsName = sbParamsName.Remove(sbParamsName.Length - 1, 1)

            Dim pkName = params.Where(Function(x) x.Key.Equals(PrimaryKeyName, StringComparison.CurrentCultureIgnoreCase)).FirstOrDefault().Value.ParameterName

            Dim strSql = getUpdateQuery(TableName, _
                                        sbParamsName.ToString(), _
                                        String.Format("Where {0} = {1}", PrimaryKeyName, pkName) _
                                        )

            Me.ExecuteTextNonQuery(strSql, params.Select(Function(x) x.Value).ToArray())

        Catch ex As Exception
            Throw ex
        Finally
            Me.Close()
        End Try

    End Sub
    Private _getCurrentDTO As T
    Public Function getCurrentDTO() As T
        If _getCurrentDTO Is Nothing Then
            _getCurrentDTO = Activator.CreateInstance(Of T)()
        End If
        Return _getCurrentDTO
    End Function

    Private _getCurrentEntityPropertiesAttributes As Dictionary(Of String, PropertiesAttributes)
    Public Function getCurrentEntityPropertiesAttributes() As Dictionary(Of String, PropertiesAttributes)
        If _getCurrentEntityPropertiesAttributes Is Nothing Then
            _getCurrentEntityPropertiesAttributes = CType(CObj(getCurrentDTO()), Entity.EntityBase).PropertiesAttributes()
        End If
        Return _getCurrentEntityPropertiesAttributes
    End Function


    Public Sub New()
        MyBase.New()
        Try

            If Not TypeOf getCurrentDTO() Is EntityBase Then
                Throw New ApplicationException("la clase " & getCurrentDTO().GetType().FullName & " no implementa EntityBase ")
            End If
            Dim ClsAtt = CType(CObj(getCurrentDTO()), Entity.EntityBase).ClassAttribute()
            If ClsAtt Is Nothing Then
                Throw New ApplicationException("la clase " & getCurrentDTO().GetType().FullName & " no implementa ClassAttribute ")
            End If
            MyBase.ConnectionStringEntry = ClsAtt.DataBaseName

            If Me.ConnectionType = ConnectionTypeEnum.Oracle Then
                If Not ClsAtt.IsNotAddEntity Then
                    verifySequence(getTableName(), CType(CObj(getCurrentDTO()), Entity.EntityBase).getPrimaryKeyProperty.FieldMappingAtt.DbFieldMapp)
                End If

            End If
        Catch ex As Exception
            Throw ex
        End Try

    End Sub
End Class
